﻿using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Security.Cryptography;
using System.Web;

namespace phpBB
{
    /// <summary>
    /// Extensions-class
    /// </summary>
    public static class phpBB_Extensions
    {
        /// <summary>
        /// Extender class that converts a DateTime to a Unix Timestamp
        /// Usage: dtDateTime.ToUnixTimestamp()
        /// </summary>
        /// <param name="dtTime"></param>
        /// <returns>UnixTimestamp</returns>
        public static int ToUnixTimestamp(this DateTime dtTime)
        {
            DateTime Date1 = new DateTime(1970, 1, 1);  // reference date
            TimeSpan ts = new TimeSpan(dtTime.Ticks - Date1.Ticks);  // calculate the delta
            // The delta in seconds is the Unix timestamp
            return (Convert.ToInt32(ts.TotalSeconds));
        }
    }

    /// <summary>
    /// Methods that enable the connections to the phpBB-Forum
    /// </summary>
    class phpBBDatabaseInterface
    {
        /// <summary>
        /// Singleton pattern
        /// </summary>
        private static phpBBDatabaseInterface instance = null;
        public static phpBBDatabaseInterface getSingleton() 
        { 
            if (instance == null)
                instance = new phpBBDatabaseInterface(); 
            return instance; 
        }

        /// <summary>
        /// Constructor
        /// </summary>
        public phpBBDatabaseInterface()
        {
            ConnectionString = "";
            ProviderName = "";
            TablePrefix = "phpbb_";
            Initialized = false;
        }

        #region Attributes
        /// <summary>
        /// ConnectionString: Automatic attribute
        /// </summary>
        public string ConnectionString
        {
            get;
            private set;
        }

        /// <summary>
        /// ProviderName: Automatic attribute
        /// </summary>
        public string ProviderName
        {
            get;
            private set;
        }

        /// <summary>
        /// TablePrefix: Automatic attribute
        /// </summary>
        public string TablePrefix
        {
            get;
            private set;
        }

        /// <summary>
        /// CookieName: Automatic attribute
        /// </summary>
        public string CookieName
        {
            get;
            private set;
        }

        /// <summary>
        /// RegistrationURL: Automatic attribute
        /// </summary>
        public string RegistrationURL
        {
            get;
            private set;
        }

        /// <summary>
        /// Initialized: Automatic attribute
        /// </summary>
        private bool Initialized
        {
            get;
            set;
        }
        #endregion

        /// <summary>
        /// Returns the table name. The prefix is added to the table name
        /// </summary>
        /// <param name="PlainName"></param>
        /// <returns>Table name with prefix</returns>
        private string GetTableName(string PlainName)
        {
            return TablePrefix + PlainName;
        }

        /// <summary>
        /// Connects to the arbitrary database and returns a DbCommand
        /// </summary>
        /// <returns>DbCommand of arbitrary provider</returns>
        private DbCommand ConnectToDatabase()
        {
            DbCommand cmd = null;

            if (ConnectionString != null)
            {
                try
                {
                    // Factory to create a connection
                    DbProviderFactory factory =
                        DbProviderFactories.GetFactory(ProviderName);

                    DbConnection conn = factory.CreateConnection();
                    conn.ConnectionString = ConnectionString;

                    cmd = conn.CreateCommand();
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in method phpBBDatabaseInterface.ConnectToDatabase: " + ex.Message);
                }
            }
            return cmd;
        }

        /// <summary>
        /// Initialization reads the connectionstring from the NameValueCollection config,
        /// connects to the database and reads additional attributs so that they don't have
        /// to be set in the config.
        /// </summary>
        /// <param name="config"></param>
        public void Initialize(NameValueCollection config)
        {
            if (!Initialized)
            {
                CookieName = "";
                RegistrationURL = "";

                ConnectionString = ConfigurationManager.ConnectionStrings[config["connectionStringName"]].ConnectionString;
                ProviderName = ConfigurationManager.ConnectionStrings[config["connectionStringName"]].ProviderName;
                if (config["tablePrefix"] != null)
                {
                    TablePrefix = config["tablePrefix"];
                }

                using (DbCommand cmd = ConnectToDatabase())
                {
                    cmd.CommandText = "SELECT config_value FROM " + GetTableName("config") + " WHERE config_name = 'cookie_name'";
                    cmd.Connection.Open();
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            CookieName = reader.GetString(0);
                            reader.Close();
                        }
                    }

                    string server_protocol = "";
                    string server_name = "";
                    string script_path = "";
                    cmd.CommandText = "SELECT config_value FROM " + GetTableName("config") + " WHERE config_name = 'server_protocol'";
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            server_protocol = reader.GetString(0);
                            reader.Close();
                        }
                    }
                    cmd.CommandText = "SELECT config_value FROM " + GetTableName("config") + " WHERE config_name = 'server_name'";
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            server_name = reader.GetString(0);
                            reader.Close();
                        }
                    }
                    cmd.CommandText = "SELECT config_value FROM " + GetTableName("config") + " WHERE config_name = 'script_path'";
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            script_path = reader.GetString(0);
                            reader.Close();
                        }
                    }
                    // URL that links to the phpBB registration page
                    RegistrationURL = server_protocol + server_name + script_path + @"/ucp.php?mode=register";

                    cmd.Connection.Close();
                }
                Initialized = true;
            }
        }

        #region Methods for MembershipProvider 
        
        /// <summary>
        /// Validates a user. The given password is hashed and then compared to the saved password
        /// </summary>
        /// <param name="username"></param>
        /// <param name="password"></param>
        /// <returns>Bool if user is validated or not</returns>
        public bool ValidateUser(string username, string password)
        {
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT user_password FROM " + GetTableName("users") + " WHERE username = '" + username + "' and user_inactive_reason = 0";
                string pwd;
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        pwd = reader.GetString(0);
                    }
                    else
                    {
                        return false;
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
                if (!phpbb_check_hash(password, pwd))
                {
                    return false;
                }
                return true;
            }
        }

        /// <summary>
        /// Creates the phpBB sessions and at the end the necessary cookies 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="rememberMe"></param>
        public void SetCookies(string username, bool rememberMe)
        {
            try
            {
                string UserID = "1";
                string SessionID = "";
                string UserLastVisit = "";
                string SessionKey = "";

                using (DbCommand cmd = ConnectToDatabase())
                {
                    cmd.CommandText = "SELECT user_id, user_lastvisit FROM " + GetTableName("users") + " WHERE username = '" + username + "' and user_inactive_reason = 0";
                    cmd.Connection.Open();
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            UserID = reader.GetInt32(0).ToString();
                            UserLastVisit = reader.GetInt32(1).ToString();
                            reader.Close();
                        }
                    }

                    cmd.CommandText = "SELECT session_id FROM " + GetTableName("sessions") + " WHERE session_user_id = '" + UserID + "' order by session_start desc";
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            SessionID = reader.GetString(0);
                            reader.Close();
                        }
                        else
                        {
                            SessionID = System.Guid.NewGuid().ToString("N");
                            reader.Close();
                            cmd.CommandText = "INSERT INTO " + GetTableName("sessions") +
                                " ( session_id, session_user_id, session_forum_id, session_last_visit, session_start, session_time, session_ip, session_browser, session_forwarded_for, session_page, session_viewonline, session_autologin, session_admin ) " +
                                string.Format("values ('{0}', {1}, {2}, {3}, {4}, {5}, '{6}', '{7}', '{8}', '{9}', {10}, {11}, {12} ) ",
                                SessionID, UserID, 0, UserLastVisit, DateTime.Now.ToUnixTimestamp(), DateTime.Now.ToUnixTimestamp(), HttpContext.Current.Request.UserHostAddress, HttpContext.Current.Request.UserAgent.Substring(0,150), "", "", 1, rememberMe ? 1 : 0, 0);
                            cmd.ExecuteNonQuery();
                        }
                    }

                    // rememberMe 
                    cmd.CommandText = "SELECT key_id FROM " + GetTableName("sessions_keys") + " WHERE user_id = " + UserID + " ORDER BY last_login DESC";
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            SessionKey = reader.GetString(0);
                            reader.Close();
                        }
                    }

                    if (rememberMe)
                    {
                        SessionKey = System.Guid.NewGuid().ToString("N");
                        if (SessionKey == "")
                        {
                            cmd.CommandText = "INSERT INTO " + GetTableName("sessions_keys") + " ( key_id, user_id, last_ip, last_login ) " +
                                string.Format("values('{0}', {1}, '{2}', {3} )'", SessionKey, UserID, HttpContext.Current.Request.UserHostAddress, DateTime.Now.ToUnixTimestamp());
                        }
                        else
                        {
                            cmd.CommandText = "UPDATE " + GetTableName("sessions_keys") +
                                string.Format(" SET key_id = '{0}', last_ip = '{2}', last_login = {3} WHERE user_id = {1}", SessionKey, UserID, HttpContext.Current.Request.UserHostAddress, DateTime.Now.ToUnixTimestamp());
                        }
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        SessionKey = "";
                        cmd.CommandText = "DELETE FROM " + GetTableName("sessions_keys") + string.Format(" WHERE user_id = {0}", UserID);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = "DELETE FROM " + GetTableName("sessions") + string.Format(" WHERE session_user_id = {0} AND session_id <> '{1}'", UserID, SessionID);
                        cmd.ExecuteNonQuery();
                    }

                    cmd.Connection.Close();
                }
                SetCookies(UserID, SessionID, SessionKey);
            }
            catch (Exception ex)
            {
                throw new Exception("Fehler in Methode phpBBDatabaseInterface.SetCookies: " + ex.Message);
            }
        }

        /// <summary>
        /// Private method that sets the phpBB cookies.
        /// </summary>
        /// <param name="UserID"></param>
        /// <param name="SessionID"></param>
        /// <param name="SessionKey"></param>
        private void SetCookies(string UserID, string SessionID, string SessionKey)
        {
            int AutoLoginValue = 0;
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT config_value FROM " + GetTableName("config") + " WHERE config_name = 'max_autologin_time'";
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        AutoLoginValue = int.Parse(reader.GetString(0));
                        reader.Close();
                    }
                }
                cmd.Connection.Close();
            }
            if (AutoLoginValue == 0)
                AutoLoginValue = 365;

            HttpCookie Cookie = new HttpCookie(CookieName + "_u", UserID);
            Cookie.Expires = DateTime.Now.AddDays(AutoLoginValue);
            HttpContext.Current.Response.Cookies.Add(Cookie);
            Cookie = new HttpCookie(CookieName + "_sid", SessionID);
            Cookie.Expires = DateTime.Now.AddDays(AutoLoginValue);
            HttpContext.Current.Response.Cookies.Add(Cookie);
            Cookie = new HttpCookie(CookieName + "_k", SessionKey);
            Cookie.Expires = DateTime.Now.AddDays(AutoLoginValue);
            HttpContext.Current.Response.Cookies.Add(Cookie);
        }

        /// <summary>
        /// Removes the cookies. 
        /// Maybe I'll have to do more here to end the phpBB session.
        /// </summary>
        public void SignOut()
        {
            HttpCookie SidCookie = HttpContext.Current.Request.Cookies[CookieName + "_sid"];
            if ( SidCookie != null )
            {
                string Sid = SidCookie.Value;
                if (Sid != null && Sid != "")
                {
                    using (DbCommand cmd = ConnectToDatabase())
                    {
                        cmd.CommandText = "DELETE FROM " + GetTableName("sessions") + string.Format(" WHERE session_id = '{0}'", Sid);
                        cmd.Connection.Open();
                        cmd.ExecuteNonQuery();
                        cmd.Connection.Close();
                    }
                }
            }

            HttpContext.Current.Response.Cookies[CookieName + "_u"].Value = "1";
            HttpContext.Current.Response.Cookies[CookieName + "_sid"].Value = "";
            HttpContext.Current.Response.Cookies[CookieName + "_k"].Value = "";
        }

        /// <summary>
        /// Reads the phpBB cookies and checks if the user is authenticated.
        /// The UserName and rememberMe variable are returned
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="rememberMe"></param>
        /// <returns>bool if phpBB authentication exists</returns>
        public bool IsAuthenticated(out string UserName, out bool rememberMe)
        {
            bool SessionExists = false;
            UserName = "";
            rememberMe = false;

            HttpCookie Cookie_u = HttpContext.Current.Request.Cookies[CookieName + "_u"]; ;
            HttpCookie Cookie_sid = HttpContext.Current.Request.Cookies[CookieName + "_sid"]; ;
            HttpCookie Cookie_k = HttpContext.Current.Request.Cookies[CookieName + "_k"]; ;

            string UserID = (Cookie_u != null && Cookie_u.Value != null) ? Cookie_u.Value : "";
            string SessionID = (Cookie_sid != null && Cookie_sid.Value != null) ? Cookie_sid.Value : "";
            string SessionKey = (Cookie_k != null && Cookie_k.Value != null) ? Cookie_k.Value : "";

            if (UserID != "" && UserID != "1")
            {
                using (DbCommand cmd = ConnectToDatabase())
                {
                    cmd.CommandText = "SELECT username FROM " + GetTableName("users") + " WHERE user_id = '" + UserID + "' and user_inactive_reason = 0";
                    cmd.Connection.Open();
                    using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            UserName = reader.GetString(0);
                            reader.Close();
                        }
                    }

                    // phpBB Session prüfen
                    if (SessionID != "" && UserID != "")
                    {
                        cmd.CommandText = "SELECT 1 FROM " + GetTableName("sessions") + " WHERE session_id = '" + SessionID + "' and session_user_id = " + UserID;
                        using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            if (reader.HasRows)
                            {
                                SessionExists = true;
                            }

                        }
                    }
                    cmd.Connection.Close();
                }
            }

            return SessionExists;
        }
        #endregion

        #region Crypto-methods of phpBB
        
        // Thanks to Fabrizio Caldarelli [fabriziocaldarelli@negusweb.it]

        /// <summary>
        /// Hash an input string and return the hash as
        /// a 32 character hexadecimal string.
        /// </summary>
        /// <param name="input"></param>
        /// <returns>md5 hash of given input as sting</returns>
        private string md5(string input)
        {
            // Create a new instance of the MD5CryptoServiceProvider object.
            MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

            // Create a new Stringbuilder to collect the bytes
            // and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data 
            // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

        /// <summary>
        /// Hash an input string and return the hash as
        /// a 32 character hexadecimal string.
        /// </summary>
        /// <param name="input"></param>
        /// <returns>md5 hash of given input as byte[]</returns>
        private byte[] md5bin(string input)
        {
            // Create a new instance of the MD5CryptoServiceProvider object.
            MD5CryptoServiceProvider md5Hasher = new MD5CryptoServiceProvider();

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(input));

            return data;
        }

        /// <summary>
        /// Converts a byte into a int
        /// </summary>
        /// <param name="ch"></param>
        /// <returns>int of byte ch</returns>
        private int ord(byte ch)
        {
            return ((int)ch);
        }

        /// <summary>
        /// Creates a unique ID
        /// </summary>
        /// <returns>Unique ID</returns>
        private string unique_id()
        {
            double val = DateTime.UtcNow.ToUnixTimestamp();
            String vals = md5(val.ToString());

            return vals.Substring(4, 16);
        }
        

        /// <summary>
        /// @version Version 0.1 / slightly modified for phpBB 3.0.x (using $H$ as hash type identifier)
        ///
        /// Portable PHP password hashing framework.
        ///
        /// Written by Solar Designer <solar at openwall.com> in 2004-2006 and placed in
        /// the public domain.
        ///
        /// There's absolutely no warranty.
        ///
        /// The homepage URL for this framework is:
        ///
        ///	http://www.openwall.com/phpass/
        ///
        /// Please be sure to update the Version line if you edit this file in any way.
        /// It is suggested that you leave the main version number intact, but indicate
        /// your project name (after the slash) and add your own revision information.
        ///
        /// Please do not change the "private" password hashing method implemented in
        /// here, thereby making your hashes incompatible.  However, if you must, please
        /// change the hash type identifier (the "$P$") to something different.
        ///
        /// Obviously, since this code is in the public domain, the above are not
        /// requirements (there can be none), but merely suggestions.
        ///
        /// Hash the password
        ///
        /// </summary>
        /// <param name="password"></param>
        /// <returns>hashed password</returns>
        private String phpbb_hash(String password)
        {
            String itoa64 = "./0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";

            String random_state = unique_id();
            List<byte> random = new List<byte>();
            int count = 6;

            if (random.Count < count)
            {
                random = new List<byte>();

                for (int i = 0; i < count; i += 16)
                {
                    random_state = md5(unique_id() + random_state);
                    random.AddRange(md5bin(random_state));
                }
                random = random.GetRange(0, count);
            }

            String hashGensaltPrivate = _hash_gensalt_private(random.ToArray(), itoa64, 6);
            String hash = _hash_crypt_private(password, hashGensaltPrivate, itoa64);

            if (hash.Length == 34)
            {
                return hash;
            }

            return md5(password);
        }

        /// <summary>
        /// Check for correct password
        /// </summary>
        /// <param name="password"></param>
        /// <param name="hash"></param>
        /// <returns>Bool if password and hash fit together</returns>
        private bool phpbb_check_hash(String password, String hash)
        {
            String itoa64 = "./0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz";
            if (hash.Length == 34)
            {
                return (_hash_crypt_private(password, hash, itoa64) == hash) ? true : false;
            }

            return (md5(password) == hash) ? true : false;
        }

        /// <summary>
        /// Generate salt for hash generation
        /// </summary>
        /// <param name="input"></param>
        /// <param name="itoa64"></param>
        /// <param name="iteration_count_log2"></param>
        /// <returns>salt for hash generation</returns>
        private string _hash_gensalt_private(byte[] input, String itoa64, int iteration_count_log2)
        {
            if (iteration_count_log2 < 4 || iteration_count_log2 > 31)
            {
                iteration_count_log2 = 8;
            }

            String output = "$H$";
            output += itoa64[Math.Min(iteration_count_log2 + 5, 30)];
            output += _hash_encode64(input, 6, itoa64);

            return output;
        }

        /// <summary>
        /// Encode hash
        /// </summary>
        /// <param name="input"></param>
        /// <param name="count"></param>
        /// <param name="itoa64"></param>
        /// <returns>Encoded hash</returns>
        private string _hash_encode64(byte[] input, int count, String itoa64)
        {
            String output = "";
            int i = 0;

            do
            {
                int value = ord(input[i++]);
                output += itoa64[value & 0x3f];

                if (i < count)
                {
                    value |= ord(input[i]) << 8;
                }

                output += itoa64[(value >> 6) & 0x3f];

                if (i++ >= count)
                {
                    break;
                }

                if (i < count)
                {
                    value |= ord(input[i]) << 16;
                }

                output += itoa64[(value >> 12) & 0x3f];

                if (i++ >= count)
                {
                    break;
                }

                output += itoa64[(value >> 18) & 0x3f];
            }
            while (i < count);

            return output;
        }

        /// <summary>
        /// The crypt method/replacement
        /// </summary>
        /// <param name="password"></param>
        /// <param name="setting"></param>
        /// <param name="itoa64"></param>
        /// <returns></returns>
        private string _hash_crypt_private(String password, String setting, String itoa64)
        {
            String output = "*";

            // Check for correct hash
            if (setting.Substring(0, 3) != "$H$")
            {
                return output;
            }

            int count_log2 = itoa64.IndexOf(setting[3]);

            if (count_log2 < 7 || count_log2 > 30)
            {
                return output;
            }

            int count = 1 << count_log2;
            string salt = setting.Substring(4, 8);

            if (salt.Length != 8)
            {
                return output;
            }

            // We're kind of forced to use MD5 here since it's the only
            // cryptographic primitive available in all versions of PHP
            // currently in use.  To implement our own low-level crypto
            // in PHP would result in much worse performance and
            // consequently in lower iteration counts and hashes that are
            // quicker to crack (by non-PHP code).
            byte[] hashBin = md5bin(salt + password);
            do
            {
                hashBin = md5bin(ASCIIEncoding.Default.GetString(hashBin) + password);
            }
            while (--count != 0);

            output = setting.Substring(0, 12);
            output += _hash_encode64(hashBin, 16, itoa64);

            return output;
        }
        #endregion
        
        #region Methods for RoleProvider

        /// <summary>
        /// Uses GetRolesForUser to check if User is in a specific role 
        /// </summary>
        /// <param name="username"></param>
        /// <param name="roleName"></param>
        /// <returns>Bool if user is in given role</returns>
		public bool IsUserInRole(string UserName, string RoleName )
        {
            return new List<string>(GetRolesForUser(UserName)).Contains(RoleName);
        }

        /// <summary>
        /// Reads all groups from phpBB database where User is member.
        /// </summary>
        /// <param name="username"></param>
        /// <returns>All roles of user</returns>
        public string[] GetRolesForUser(string UserName)
        {
            List<string> Roles = new List<string>();
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT G.group_name FROM " + GetTableName("users") + " U " +
                                  "INNER JOIN " + GetTableName("user_group") + " UG " +
                                  "ON U.user_id = UG.user_id " +
                                  "INNER JOIN " + GetTableName("groups") + " G " +
                                  "ON G.group_id = UG.group_id " +
                                  "WHERE U.username = '" + UserName + "' AND U.user_inactive_reason = 0 " +
                                  "  AND UG.user_pending = 0 ";
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Roles.Add(reader.GetString(0));
                        }
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
            }
            return Roles.ToArray();
        }
    
        /// <summary>
        /// Checks if a given role exists in the phpBB database
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns>bool if role exists</returns>
        public bool RoleExists(string RoleName)
        {
            bool Exists = false;
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT 1 FROM " + GetTableName("groups") +
                                  " WHERE group_name = '" + RoleName + "'";
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
                {
                    if (reader.HasRows)
                    {
                        Exists = true;
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
            }
            return Exists;
        }

        /// <summary>
        /// Reads all users in the given role
        /// </summary>
        /// <param name="roleName"></param>
        /// <returns>Users in given role as string[]</returns>
        public string[] GetUsersInRole(string RoleName)
        {
            List<string> Users = new List<string>();
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT U.username FROM " + GetTableName("users") + " U " +
                                  "INNER JOIN " + GetTableName("user_group") + " UG " +
                                  "ON U.user_id = UG.user_id " +
                                  "INNER JOIN " + GetTableName("groups") + " G " +
                                  "ON G.group_id = UG.group_id " +
                                  "WHERE G.group_name = '" + RoleName + "'  AND U.user_inactive_reason = 0 " +
                                  "  AND UG.user_pending = 0 ";
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Users.Add(reader.GetString(0));
                        }
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
            }
            return Users.ToArray();
        }

        /// <summary>
        /// Reads all rows (groups) of phpBB database
        /// </summary>
        /// <returns>all roles as string[]</returns>
        public string[] GetAllRoles()
        {
            List<string> Roles = new List<string>();
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT group_name FROM " + GetTableName("groups");
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Roles.Add(reader.GetString(0));
                        }
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
            }
            return Roles.ToArray();
        }

        /// <summary>
        /// Searches users that match the given filter and are in the given role
        /// </summary>
        /// <param name="roleName"></param>
        /// <param name="usernameToMatch"></param>
        /// <returns>users in string[]</returns>
        public string[] FindUsersInRole(string RoleName, string UsernameToMatch)
        {
            List<string> Users = new List<string>();
            using (DbCommand cmd = ConnectToDatabase())
            {
                cmd.CommandText = "SELECT U.username FROM " + GetTableName("users") + " U " +
                                  "INNER JOIN " + GetTableName("user_group") + " UG " +
                                  "ON U.user_id = UG.user_id " +
                                  "INNER JOIN " + GetTableName("groups") + " G " +
                                  "ON G.group_id = UG.group_id " +
                                  "WHERE G.group_name = '" + RoleName + "'  AND U.user_inactive_reason = 0 " +
                                  "  AND UG.user_pending = 0 " +
                                  "  AND U.username like '" + UsernameToMatch + "'";
                cmd.Connection.Open();
                using (DbDataReader reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Users.Add(reader.GetString(0));
                        }
                    }
                    reader.Close();
                }
                cmd.Connection.Close();
            }
            return Users.ToArray();
        }
	    #endregion    
    }
}
